package com.yincheng.oes.business.util;

import com.yincheng.oes.business.consist.Consists;
import com.yincheng.oes.business.group.model.dto.GroupUserExcelTitleDTO;
import com.yincheng.oes.business.group.model.dto.UserPaperExcelTitleDTO;
import com.yincheng.oes.business.util.model.ExcelCell;
import java.awt.Color;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;

/**
 * @Author: yangsy
 * @Contact: 446948434@qq.com
 * @Date: 2021/8/24 21:21
 * @Version: 1.0
 * @Description: 班级学员信息导出excel工具类
 */
public class GroupUserExcelUtils {

    private static final Logger logger = LogManager.getLogger(GroupUserExcelUtils.class);

    private final XSSFColor RED = new XSSFColor(new Color(250, 56, 56));
    private final XSSFColor YELLOW = new XSSFColor(new Color(246, 192, 34));
    private final XSSFColor GREEN = new XSSFColor(new Color(21, 128, 8));
    private final XSSFColor GRAY = new XSSFColor(new Color(147, 143, 143));
    private final XSSFColor WHITE = new XSSFColor(new Color(255, 255, 255));
    private final XSSFColor BLACK = new XSSFColor(new Color(0, 0, 0, 178));

    // 导出数据
    public void export(OutputStream os) throws Exception {
        wb.write(os);
        os.flush();
    }

    protected XSSFWorkbook wb;
    protected XSSFCellStyle titleCellStyle;

    public GroupUserExcelUtils(GroupUserExcelTitleDTO titles, List<List<ExcelCell>> dataList) {
        this.wb = new XSSFWorkbook();
        this.titleCellStyle = createTitleCellStyle();
        XSSFSheet sheet = wb.createSheet("sheet0");
        setTitle(sheet, titles);
        logger.debug("after set title");
        setContext(sheet, dataList);
        logger.debug("after set context");
        setSheetAutoSizeColumn(sheet);
        logger.debug("after set autoSizeColumn");
    }

    protected void setContext(XSSFSheet sheet, List<List<ExcelCell>> rows) {
        int i = 2;
        for (List<ExcelCell> row : rows) {
            XSSFRow fRow = sheet.createRow(i);

            int j = 0;
            for (ExcelCell data : row) {
                XSSFCell cell = fRow.createCell(j);
                cell.setCellStyle(createContentStyle(data));
                cell.setAsActiveCell();

                String value = getFieldValue(data.getValue());
                cell.setCellValue(value);
                j++;
            }

            i++;
        }
    }

    private XSSFCellStyle createContentStyle(ExcelCell data) {
        XSSFCellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(WHITE);
        if (Objects.nonNull(data.getExcelStyle())) {
            if (com.yincheng.oes.business.consist.Consists.GREEN.equals(data.getExcelStyle().getBgColor())) {
                style.setFillForegroundColor(GREEN);
            }
            if (com.yincheng.oes.business.consist.Consists.YELLOW.equals(data.getExcelStyle().getBgColor())) {
                style.setFillForegroundColor(YELLOW);
            }
            if (Consists.RED.equals(data.getExcelStyle().getBgColor())) {
                style.setFillForegroundColor(RED);

            }
        }
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFont(createContentFont());
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.LEFT);// 居左
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        return style;
    }

    protected void setTitle(XSSFSheet sheet, GroupUserExcelTitleDTO titles) {
        XSSFRow head = sheet.createRow(0);
        XSSFRow secRow = sheet.createRow(1);
        List<String> baseTitles = titles.getBaseTitles();
        for (int n = 0; n < baseTitles.size(); n++) {
            XSSFCell cell = head.createCell(n);
            cell.setCellType(CellType.STRING);
            XSSFRichTextString text = new XSSFRichTextString(baseTitles.get(n));
            cell.setCellValue(text);
            cell.setCellStyle(titleCellStyle);
            cell.setAsActiveCell();
            CellRangeAddress region = new CellRangeAddress(0, 1, n, n);
            sheet.addMergedRegion(region);
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
        }
        List<UserPaperExcelTitleDTO> userPaperExcelTitleList = titles.getUserPaperExcelTitleList();
        for (int n = 0; n < userPaperExcelTitleList.size(); n++) {
            XSSFCell cell = head.createCell(3 + 3 * n);
            cell.setCellType(CellType.STRING);
            XSSFRichTextString text = new XSSFRichTextString(userPaperExcelTitleList.get(n).getPaperName());
            cell.setCellValue(text);
            cell.setCellStyle(titleCellStyle);
            cell.setAsActiveCell();
            CellRangeAddress region = new CellRangeAddress(0, 0, 3 + 3 * n, 3 + 3 * (n + 1) - 1);
            sheet.addMergedRegion(region);
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);

            List<String> childTitles = userPaperExcelTitleList.get(n).getChildTitles();

            for (int m = 0; m < childTitles.size(); m++) {
                XSSFCell childCell = secRow.createCell(3 + 3 * n + m);
                childCell.setCellType(CellType.STRING);
                XSSFRichTextString childText = new XSSFRichTextString(childTitles.get(m));
                childCell.setCellValue(childText);
                childCell.setCellStyle(titleCellStyle);
                childCell.setAsActiveCell();
            }
        }
    }

    private String getFieldValue(Object value) {
        if (value instanceof Date) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            return sdf.format(value);
        }
        return value == null ? "" : value.toString();
    }

    protected void setSheetAutoSizeColumn(XSSFSheet sheet) {
        //sheet的索引从2开始,获取sheet列数
        int maxColumn = sheet.getRow(2).getPhysicalNumberOfCells();
        for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            // 遍历列的数据，获取这一列的最长字符串
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                XSSFRow currentRow;
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    XSSFCell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellTypeEnum() == CellType.STRING) {
                        //int length = currentCell.getStringCellValue().getBytes().length;
                        //上面是网上所有currentCell获取length答案，但试过后发现偏大，在一个文章下的评论区看到下面的计算,试了一下，列宽实现了自适应大小，还没搞懂原因
                        //length = (byte长度+string长度)/2，有了解的麻烦，麻烦留言解释一下，感谢！！
                        int length = (currentCell.getStringCellValue().getBytes(StandardCharsets.UTF_8).length + currentCell.toString().length()) / 2;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            //将最长的length*256设为列宽
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

    /**
     * 创建每列的内容的样式
     */
    protected XSSFCellStyle createTitleCellStyle() {
        XSSFCellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(GRAY);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.LEFT);// 居左
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setFont(createTitleFont());// 把字体应用到当前的样式

        return style;
    }

    /**
     * 设置每列的内容的文字样式
     */
    protected XSSFFont createContentFont() {
        XSSFFont font = wb.createFont();
        short st = 11;
        font.setColor(BLACK);

        font.setFontHeightInPoints(st);
        return font;
    }

    /**
     * 创建每列的内容的文字样式
     */
    protected XSSFFont createTitleFont() {
        XSSFFont font = wb.createFont();
        font.setColor(BLACK);
//        font.setBold(true);// 设置字体为加粗
        font.setFontHeightInPoints((short) 12);// 设置字体大小为12号
        return font;
    }
}
